 -- drop table asm_test_1;
Truncate table asm_DW_CONTRATTO_ST2;
insert into asm_DW_CONTRATTO_ST2 
 select distinct te.cuf_id,te.ver_num, v.parot_id,p.parot_des,parot_valvis,  v.VPXCO_VAL
                --bulk collect into parotid, pardes,visin,valin
                from valparxcufot v,parot p ,lvparot l ,asm_DW_CONTRATTO_ST1   te
                 where v.cufot_vernum =  te.ver_num --459194 \
                
                  and te.ver_dtfine =to_date('31129999','ddmmyyyy')
                and v.ver_dtfine =to_date('31129999','ddmmyyyy')
                                and          (SELECT CASE
                                     WHEN ista.iss_staid <> 0
                                     AND ista.iss_staidsuper = 0
                                     AND ista.iss_sstaidsuper = 0
                                        THEN ista.iss_staid
                                     WHEN ista.iss_staid <> 0
                                     AND ista.iss_staidsuper <> 0
                                     AND ista.iss_sstaidsuper = 0
                                        THEN ista.iss_staidsuper
                                     WHEN ista.iss_staid <> 0
                                     AND ista.iss_staidsuper <> 0
                                     AND ista.iss_sstaidsuper <> 0
                                        THEN ista.iss_sstaidsuper
                                     ELSE -1
                                  END
                                    FROM ista ista
                                     WHERE ista.az_id = v.az_id
                                    AND ista.csi_id = v.csi_id
                                     AND ista.ista_verstato = v.ista_verstato) = 2
                   and      p.az_id = '01'
                   
                  AND p.ot_id = v.ot_id
                  AND p.cparot_id = v.cparot_id
                  AND p.parot_id = v.parot_id
                and   l.az_id(+) = '01'
                and      v.az_id = '01'
              AND   v.ot_id=l.ot_id(+)
              AND  v.cparot_id=l.cparot_id(+) 
              AND  v.parot_id=l.parot_id(+) 
              AND  v.vpxco_val= l.parot_val(+);
